package com.realshare.frame.util.excel;

import com.realshare.frame.util.excel.model.ExcelEntity;
import com.realshare.frame.util.excel.model.ExcelRow;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.List;


public class WriteExcel {

    public boolean writeByModel(String sourcePath, String modelPath, List<List<String>> rowList) {
        try {
            FileInputStream fis = new FileInputStream(modelPath);
            XSSFWorkbook workBook = new XSSFWorkbook(fis);
            OutputStream out = new FileOutputStream(sourcePath);
            //进行模板的克隆
            XSSFSheet sheet = workBook.cloneSheet(0);
            //移除workbook中的模板sheet
            workBook.removeSheetAt(0);
            //给sheet命名
            workBook.setSheetName(workBook.getSheetIndex(sheet), "Sheet1");
            workBook.setSheetOrder("Sheet1", 0);
            workBook.setActiveSheet(0);
            for (int rowIndex = 0; rowIndex < rowList.size(); rowIndex++) {
                List<String> row = rowList.get(rowIndex);
                XSSFRow xssfRow = sheet.getRow(rowIndex);
                for (int cellIndex = 0; cellIndex < row.size(); cellIndex++) {
                    String cellValue = row.get(cellIndex);
                    XSSFCell xssfCell = xssfRow.getCell(cellIndex);
                    if (xssfCell != null) {
                        xssfCell.setCellValue(cellValue);
                    }
                }
            }
            workBook.write(out);
            fis.close();
            out.flush();
            out.close();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 复制excel
     *
     * @param sourcePath 原路径
     * @param modelPath  模板路径
     * @return
     */
    public boolean writeByModel(String sourcePath, String modelPath) {
        try {
            List<List<String>> rowList = ExcelUtils.readExcel(sourcePath);
            writeByModel(sourcePath, modelPath, rowList);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }


    /**
     * 通用导出excel
     *
     * @param entity 需要导出的数据
     */
    public File writeExcel(ExcelEntity entity) {
        /**
         keep 500 rows in memory, exceeding rows will be flushed to disk
         此种方式如果再写入的过程中再去sheet中取哪一行的时候取不到,
         个人认为是因为大部分数据都写入了硬盘了,
         并没有在sheet内存中,所以会取不导
         */
        SXSSFWorkbook workbook = new SXSSFWorkbook(500);
        Sheet sheet = workbook.createSheet("sheet1");

        //表头样式
        XSSFCellStyle titleStyle = workbook.getXSSFWorkbook().createCellStyle();
        titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // //水平居中
        titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);//垂直居中
        titleStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框
        titleStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框
        titleStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框
        titleStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框
        titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        titleStyle.setFillForegroundColor(new XSSFColor(ExcelUtils.toColorFromString("c0c0c0")));//设置背景色

        Font font = workbook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD); //字体增粗
        titleStyle.setFont(font);

        //内容样式
        XSSFCellStyle contentStyle = workbook.getXSSFWorkbook().createCellStyle();
        contentStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // //水平居中
        contentStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);//垂直居中
        contentStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框
        contentStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框
        contentStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框
        contentStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框
        contentStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        contentStyle.setFillForegroundColor(new XSSFColor(ExcelUtils.toColorFromString("FFFFFF")));//设置背景色

        int titleRows = 0;
        if (entity.getTitles() != null && entity.getTitles().size() > 0) {
            titleRows = entity.getTitles().size();
            for (int rowIndex = 0; rowIndex < entity.getTitles().size(); rowIndex++) {
                Row row = sheet.createRow(rowIndex);
                ExcelRow cellTitles = entity.getTitles().get(rowIndex);
                for (int cellIndex = 0; cellIndex < cellTitles.getCells().size(); cellIndex++) {
                    Cell cell = row.createCell(cellIndex);
                    cell.setCellValue(cellTitles.getCells().get(cellIndex).getValue());
                    cell.setCellStyle(titleStyle);
                }
            }
        }

        if (entity.getContents() != null && entity.getContents().size() > 0) {
            for (int rowIndex = 0; rowIndex < entity.getContents().size(); rowIndex++) {
                Row row = sheet.createRow(rowIndex + titleRows);
                ExcelRow cellContents = entity.getContents().get(rowIndex);
                for (int cellIndex = 0; cellIndex < cellContents.getCells().size(); cellIndex++) {
                    Cell cell = row.createCell(cellIndex);
                    cell.setCellValue(cellContents.getCells().get(cellIndex).getValue());
                    cell.setCellStyle(contentStyle);
                }
            }
        }

        try {
            File file = new File(entity.getDownloadPath());
            if (!file.exists()) {
                boolean mkdirs = file.mkdirs();
            }
            file = new File(entity.getDownloadPath() + entity.getExcelName());
            if (file.exists()) {
                boolean mkdir = file.mkdir();
            }
            FileOutputStream outputStream = new FileOutputStream(file);
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            return file;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

}
